﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Collections;

namespace clBD
{
    public class BDBase
    {
        protected  OleDbConnection cn = null;
        protected OleDbTransaction trx = null;
        public BDBase(OleDbConnection cn)
        {
            this.cn = cn;
        }
        public BDBase(OleDbConnection cn, OleDbTransaction trx)
        {
            this.cn = cn;
            this.trx = trx;
        }
        public OleDbDataReader execDr(String query)
        {
            OleDbCommand cmd = new OleDbCommand();
            OleDbDataReader dr;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = query;
            cmd.Connection = cn;
            if (trx != null)
                cmd.Transaction = trx;
            dr = cmd.ExecuteReader();
            return dr;
        }
        protected void execute(String sentenc)
        {
            OleDbCommand cmd = new OleDbCommand();
            cmd.CommandText = sentenc;
            cmd.CommandType = CommandType.Text;
            cmd.Connection = this.cn;
            if (this.trx != null)
                cmd.Transaction = trx;
            cmd.ExecuteNonQuery();
        }
        public OleDbDataReader getElementos(String elemento)
        {
            String query = "select nombre " +
                "from  " + elemento +
                " order by 1";
            return execDr(query);
        }

        public void grabarElemento(String elemento, String nombre)
        {
            String query = "select 1 from " + elemento + " where nombre='" + nombre.Trim().ToUpper() + "'";
            OleDbDataReader dr = execDr(query);
            if (!dr.HasRows)
            {
                query = "insert into " + elemento + "(nombre) values ('" + nombre.Trim().ToUpper() + "')";
                execute(query);
            }
            dr.Close();
        }
        protected  Int32 getSeq(String nomSeq)
        {
            String query = "select max(seq_val) from seqmae where seq_name='" +
                nomSeq + "'";
            OleDbDataReader dr = execDr(query);
            string sentenc;
            int max;
            if (dr.Read())
            {
                if (dr.IsDBNull(0))
                {
                    max = 1;
                    sentenc = "insert into seqmae (seq_name,seq_val) values('" +
                        nomSeq + "'," + max.ToString() + ")";
                }
                else
                {
                    max = dr.GetInt32(0) + 1;
                    sentenc = "update seqmae set seq_val = " + max.ToString() +
                        " where seq_name='" + nomSeq + "'";
                }
            }
            else
            {
                max = 1;
                sentenc = "insert into seqmae (seq_name,seq_val) values('" +
                    nomSeq + "'," + max.ToString() + ")";
            }
            execute(sentenc);
            return max;
        }
        public ArrayList getArrayList(String query)
        {
            ArrayList al = new ArrayList();
            OleDbDataReader dr = execDr(query);
            while (dr.Read())
            {
                object[] values = new object[dr.FieldCount];
                dr.GetValues(values);
                al.Add(values);
            }
            return al;
        }
        public ArrayList getArrayListWithHeader(String query)
        {
            ArrayList al = new ArrayList();
            OleDbDataReader dr = execDr(query);
            object[] fields = new object[dr.FieldCount];

            DataTable schemaTable = dr.GetSchemaTable();
            for (int ix = 0; ix < schemaTable.Rows.Count;ix++ ){
                fields[ix] = schemaTable.Rows[ix];                
            }
            al.Add(fields);
            while (dr.Read())
            {
                object[] values = new object[dr.FieldCount];
                dr.GetValues(values);
                al.Add(values);
            }
            return al;
        }
        public object  getValUnico(String query)
        {
            object rpta = null;
            OleDbDataReader dr = execDr(query);
            if (dr.Read())
            {
                rpta=  dr.GetValue (0);
            }
            return rpta;
        }
    }
}
